﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PhysicalTagDA : IPhysicalTagDA
    {
        private string ADJ_QTY = "@ADJ_QTY";
        private string COUNT_BY = "@COUNT_BY";
        private string COUNT_DATE = "@COUNT_DATE";
        private string COUNT_QTY = "@COUNT_QTY";
        private string CREATED_BY = "@CREATED_BY";
        private string GROUP_NO = "@GROUP_NO";
        private string LOC = "@LOC";
        private string LOT = "@LOT";
        private string LPN_ID = "@LPN_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PHYSICAL_REQUEST_ID = "@PHYSICAL_REQUEST_ID";
        private string RECOUNT_BY = "@RECOUNT_BY";
        private string RECOUNT_DATE = "@RECOUNT_DATE";
        private string RECOUNT_QTY = "@RECOUNT_QTY";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_PHYSICAL_TAG_ID_UNIQUE = " SELECT COUNT(1) FROM @_@PHYSICAL_TAG WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO ";
        private string SQL_DELETE_PHYSICAL_TAG = " DELETE FROM @_@PHYSICAL_TAG WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string SQL_INSERT_PHYSICAL_TAG = " INSERT INTO @_@PHYSICAL_TAG ( WH_ID, PHYSICAL_REQUEST_ID, TAG_NO, GROUP_NO, TEAM_NO, OWNER_ID, SKU_ID, LOT, LOC, LPN_ID, SYS_QTY, COUNT_QTY, COUNT_BY, COUNT_DATE, RECOUNT_QTY, RECOUNT_BY, RECOUNT_DATE, ADJ_QTY, STATUS, STATUS_MSG, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, PACK_ID, UOM_ID) VALUES ( @WH_ID, @PHYSICAL_REQUEST_ID, @TAG_NO, @GROUP_NO, @TEAM_NO, @OWNER_ID, @SKU_ID, @LOT, @LOC, @LPN_ID, @SYS_QTY, @COUNT_QTY, @COUNT_BY, @COUNT_DATE, @RECOUNT_QTY, @RECOUNT_BY, @RECOUNT_DATE, @ADJ_QTY, @STATUS, @STATUS_MSG, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @PACK_ID, @UOM_ID)  ";
        private string SQL_SELECT_ALL_PHYSICAL_TAG = "SELECT  PT.ROWID, PT.WH_ID, PT.PHYSICAL_REQUEST_ID, PT.TAG_NO, PT.GROUP_NO, PT.TEAM_NO, PT.OWNER_ID, PT.SKU_ID, PT.LOT, PT.LOC, PT.LPN_ID, PT.SYS_QTY, PT.COUNT_QTY, PT.COUNT_BY, PT.COUNT_DATE, PT.RECOUNT_QTY, PT.RECOUNT_BY, PT.RECOUNT_DATE, PT.ADJ_QTY, T.[TEXT] AS STATUS_D, PT.STATUS_MSG, PT.CREATED_BY, PT.CREATED_DATE, PT.UPDATED_BY, PT.UPDATED_DATE, PT.PACK_ID, PT.UOM_ID,PT.STATUS FROM @_@PHYSICAL_TAG PT LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PT.STATUS AND SCD.SYS_CODE_TYPE = 'PCStatus' LEFT JOIN sysadm.TRANSLATION T ON 'PCStatus.'+PT.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE ";
        private string SQL_UPDATE_PHYSICAL_TAG = " UPDATE @_@PHYSICAL_TAG SET WH_ID = @WH_ID, GROUP_NO = @GROUP_NO, TEAM_NO = @TEAM_NO, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT = @LOT, LOC = @LOC, LPN_ID = @LPN_ID, SYS_QTY = @SYS_QTY, COUNT_QTY = @COUNT_QTY, COUNT_BY = @COUNT_BY, COUNT_DATE = @COUNT_DATE, RECOUNT_QTY = @RECOUNT_QTY, RECOUNT_BY = @RECOUNT_BY, RECOUNT_DATE = @RECOUNT_DATE, ADJ_QTY = @ADJ_QTY, STATUS = @STATUS, STATUS_MSG = @STATUS_MSG, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), PACK_ID = @PACK_ID, UOM_ID = @UOM_ID WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO  ";
        private string SQL_UPDATE_PHYSICAL_TAG_BY_COUNT = " UPDATE @_@PHYSICAL_TAG SET COUNT_QTY = @COUNT_QTY, COUNT_BY = @COUNT_BY, COUNT_DATE = @COUNT_DATE,STATUS = @STATUS,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO ";
        private string SQL_UPDATE_PHYSICAL_TAG_BY_RECOUNT = " UPDATE @_@PHYSICAL_TAG SET  RECOUNT_QTY = @RECOUNT_QTY, RECOUNT_BY = @RECOUNT_BY, RECOUNT_DATE = @RECOUNT_DATE, ADJ_QTY = @ADJ_QTY, STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE()  WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO ";
        private string SQL_UPDATE_PHYSICAL_TAG_STATUS = " UPDATE @_@PHYSICAL_TAG SET STATUS = @STATUS  WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string STATUS = "@STATUS";
        private string STATUS_MSG = "@STATUS_MSG";
        private string SYS_QTY = "@SYS_QTY";
        private string TAG_NO = "@TAG_NO";
        private string TEAM_NO = "@TEAM_NO";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckPhysicalTagIDUnique(string physicalTagID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID;_TAG_NO", physicalTagID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_PHYSICAL_TAG_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckPhysicalTagIDUnique(DataBase dataBase, DbTransaction tran, string physicalTagID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID;\t_TAG_NO", physicalTagID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_PHYSICAL_TAG_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeletePhysicalTag(List<string> physicalTagID, List<string> tagNo, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string strSql = this.SQL_DELETE_PHYSICAL_TAG.Replace("@_@", whLoginID) + "AND TAG_NO = @TAG_NO ";
            try
            {
                for (int j = 0; j < physicalTagID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalTagID[j]), new SqlParameter("@TAG_NO", tagNo[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeletePhysicalTag(DataBase dataBase, DbTransaction tran, List<string> physicalRequsetID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < physicalRequsetID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequsetID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PHYSICAL_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeletePhysicalTag(DataBase dataBase, DbTransaction tran, List<string> physicalTagID, List<string> tagNo, string whLoginID)
        {
            int result = 0;
            string strSql = this.SQL_DELETE_PHYSICAL_TAG.Replace("@_@", whLoginID) + "AND TAG_NO = @TAG_NO ";
            try
            {
                for (int j = 0; j < physicalTagID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalTagID[j]), new SqlParameter("@TAG_NO", tagNo[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllPhysicalTag(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllPhysicalTag(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public int GetPhysicalRequestStatus(DataBase dataBase, DbTransaction tran, string physicalTagID, string status, string whLoginID)
        {
            string strSql = " SELECT COUNT(1) FROM @_@PHYSICAL_TAG WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND STATUS !=@STATUS ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalTagID), new SqlParameter("@STATUS", status) };
            return Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras));
        }

        public PhysicalTagInfo GetPhysicalTagByID(string physicaltagID, int TagNo, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO ORDER BY TAG_NO";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@TAG_NO", TagNo), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            PhysicalTagInfo physicalTagInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    physicalTagInfo = new PhysicalTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, (reader["GROUP_NO"].ToString() != "") ? int.Parse(reader["GROUP_NO"].ToString()) : 0, reader["TEAM_NO"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECOUNT_QTY"].ToString() != "") ? decimal.Parse(reader["RECOUNT_QTY"].ToString()) : 0M, reader["RECOUNT_BY"].ToString(), (reader["RECOUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECOUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["STATUS_D"].ToString());
                }
            }
            return physicalTagInfo;
        }

        public PhysicalTagInfo GetPhysicalTagByID(DataBase dataBase, DbTransaction tran, string physicaltagID, int TagNo, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID AND TAG_NO = @TAG_NO ORDER BY TAG_NO ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@TAG_NO", TagNo), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            PhysicalTagInfo physicalTagInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                physicalTagInfo = new PhysicalTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, (reader["GROUP_NO"].ToString() != "") ? int.Parse(reader["GROUP_NO"].ToString()) : 0, reader["TEAM_NO"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECOUNT_QTY"].ToString() != "") ? decimal.Parse(reader["RECOUNT_QTY"].ToString()) : 0M, reader["RECOUNT_BY"].ToString(), (reader["RECOUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECOUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return physicalTagInfo;
        }

        public DataSet GetPhysicalTagByQueryList(List<string> sqlWhere, PhysicalTagQueryEntity physicaltagQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!physicaltagQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, physicaltagQuery.CurrentPage, physicaltagQuery.PageSize, physicaltagQuery.SortField, physicaltagQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPhysicalTagByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PhysicalTagQueryEntity physicaltagQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!physicaltagQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, physicaltagQuery.CurrentPage, physicaltagQuery.PageSize, physicaltagQuery.SortField, physicaltagQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<PhysicalTagInfo> GetPhysicalTagListByID(string physicaltagID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ORDER BY TAG_NO  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<PhysicalTagInfo> physicalTagList = new List<PhysicalTagInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    PhysicalTagInfo physicalTagInfo = new PhysicalTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, (reader["GROUP_NO"].ToString() != "") ? int.Parse(reader["GROUP_NO"].ToString()) : 0, reader["TEAM_NO"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECOUNT_QTY"].ToString() != "") ? decimal.Parse(reader["RECOUNT_QTY"].ToString()) : 0M, reader["RECOUNT_BY"].ToString(), (reader["RECOUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECOUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["STATUS_D"].ToString());
                    physicalTagList.Add(physicalTagInfo);
                }
            }
            return physicalTagList;
        }

        public List<PhysicalTagInfo> GetPhysicalTagListByID(DataBase dataBase, DbTransaction tran, string physicaltagID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ORDER BY TAG_NO  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<PhysicalTagInfo> physicalTagList = new List<PhysicalTagInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                PhysicalTagInfo physicalTagInfo = new PhysicalTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, (reader["GROUP_NO"].ToString() != "") ? int.Parse(reader["GROUP_NO"].ToString()) : 0, reader["TEAM_NO"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECOUNT_QTY"].ToString() != "") ? decimal.Parse(reader["RECOUNT_QTY"].ToString()) : 0M, reader["RECOUNT_BY"].ToString(), (reader["RECOUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECOUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["STATUS_D"].ToString());
                physicalTagList.Add(physicalTagInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return physicalTagList;
        }

        public List<string> GetPhysicalTagNoListByID(string physicaltagID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ORDER BY TAG_NO ASC  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<string> TagNo = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    TagNo.Add(reader["TAG_NO"].ToString());
                }
            }
            return TagNo;
        }

        public List<PhysicalTagInfo> GetPTReviewListByID(string physicaltagID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_TAG.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ORDER BY TAG_NO ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicaltagID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<PhysicalTagInfo> physicalTagList = new List<PhysicalTagInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    PhysicalTagInfo physicalTagInfo = new PhysicalTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, (reader["GROUP_NO"].ToString() != "") ? int.Parse(reader["GROUP_NO"].ToString()) : 0, reader["TEAM_NO"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECOUNT_QTY"].ToString() != "") ? decimal.Parse(reader["RECOUNT_QTY"].ToString()) : 0M, reader["RECOUNT_BY"].ToString(), (reader["RECOUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECOUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["STATUS_D"].ToString());
                    physicalTagList.Add(physicalTagInfo);
                }
            }
            return physicalTagList;
        }

        public int InsertPhysicalTag(PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalTag_Parameters(physicalTagInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PHYSICAL_TAG.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertPhysicalTag(DataBase dataBase, DbTransaction tran, PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalTag_Parameters(physicalTagInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PHYSICAL_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_PhysicalTag_Parameters(PhysicalTagInfo physicalTagInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PHYSICAL_REQUEST_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TAG_NO, SqlDbType.Int, 4), new SqlParameter(this.GROUP_NO, SqlDbType.Int, 4), new SqlParameter(this.TEAM_NO, SqlDbType.VarChar, 1), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.LOC, SqlDbType.VarChar, 20), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SYS_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COUNT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COUNT_BY, SqlDbType.VarChar, 20), new SqlParameter(this.COUNT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECOUNT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.RECOUNT_BY, SqlDbType.VarChar, 20), 
                new SqlParameter(this.RECOUNT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ADJ_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS_MSG, SqlDbType.VarChar, 0xff), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(physicalTagInfo.WhID))
            {
                paramArray[0].Value = physicalTagInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = physicalTagInfo.PhysicalRequestID;
            paramArray[2].Value = physicalTagInfo.TagNo;
            paramArray[3].Value = physicalTagInfo.GroupNo;
            paramArray[4].Value = physicalTagInfo.TeamNo;
            paramArray[5].Value = physicalTagInfo.OwnerID;
            paramArray[6].Value = physicalTagInfo.SkuID;
            paramArray[7].Value = physicalTagInfo.Lot;
            paramArray[8].Value = physicalTagInfo.Loc;
            paramArray[9].Value = physicalTagInfo.LpnID;
            paramArray[10].Value = physicalTagInfo.SysQty;
            paramArray[11].Value = physicalTagInfo.CountQty;
            if (!string.IsNullOrEmpty(physicalTagInfo.CountBy))
            {
                paramArray[12].Value = physicalTagInfo.CountBy;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (physicalTagInfo.CountDate != CS00003)
            {
                paramArray[13].Value = physicalTagInfo.CountDate;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            paramArray[14].Value = physicalTagInfo.RecountQty;
            if (!string.IsNullOrEmpty(physicalTagInfo.RecountBy))
            {
                paramArray[15].Value = physicalTagInfo.RecountBy;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (physicalTagInfo.RecountDate != new DateTime())
            {
                paramArray[0x10].Value = physicalTagInfo.RecountDate;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            paramArray[0x11].Value = physicalTagInfo.AdjQty;
            paramArray[0x12].Value = physicalTagInfo.Status;
            paramArray[0x13].Value = physicalTagInfo.StatusMsg;
            paramArray[20].Value = physicalTagInfo.CreatedBy;
            paramArray[0x15].Value = physicalTagInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(physicalTagInfo.PackID))
            {
                paramArray[0x16].Value = physicalTagInfo.PackID;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(physicalTagInfo.UomID))
            {
                paramArray[0x17].Value = physicalTagInfo.UomID;
                return paramArray;
            }
            paramArray[0x17].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdatePhysicalTag(PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalTag_Parameters(physicalTagInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePhysicalTag(DataBase dataBase, DbTransaction tran, PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalTag_Parameters(physicalTagInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePhysicalTagByCount(DataBase dataBase, DbTransaction tran, PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalTagInfo.PhysicalRequestID), new SqlParameter("@TAG_NO", physicalTagInfo.TagNo), new SqlParameter("@COUNT_DATE", physicalTagInfo.CountDate), new SqlParameter("@COUNT_BY", physicalTagInfo.CountBy), new SqlParameter("@STATUS", physicalTagInfo.Status), new SqlParameter("@COUNT_QTY", physicalTagInfo.CountQty), new SqlParameter("@UPDATED_BY", physicalTagInfo.CountBy) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG_BY_COUNT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePhysicalTagByReCount(DataBase dataBase, DbTransaction tran, PhysicalTagInfo physicalTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalTagInfo.PhysicalRequestID), new SqlParameter("@TAG_NO", physicalTagInfo.TagNo), new SqlParameter("@RECOUNT_DATE", physicalTagInfo.RecountDate), new SqlParameter("@RECOUNT_BY", physicalTagInfo.RecountBy), new SqlParameter("@STATUS", physicalTagInfo.Status), new SqlParameter("@RECOUNT_QTY", physicalTagInfo.RecountQty), new SqlParameter("@UPDATED_BY", physicalTagInfo.RecountBy), new SqlParameter("@ADJ_QTY", physicalTagInfo.AdjQty) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG_BY_RECOUNT.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePhysicalTagStatus(string physicalRequestID, string Status, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID), new SqlParameter("@STATUS", Status) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG_STATUS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePhysicalTagStatus(DataBase dataBase, DbTransaction tran, string physicalRequestID, string Status, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID), new SqlParameter("@STATUS", Status) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_TAG_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

